package org.liang.code.utils.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.Date;

public class ExcelGenerator {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelGenerator.class);

    public void excelgenerator (String path) throws IOException {

        // 创建新的工作簿
        try (Workbook workbook = new XSSFWorkbook()) {

            // 创建工作表
            Sheet sheet = workbook.createSheet("员工数据");

            // 创建表头行
            Row headerRow = sheet.createRow(0);

            // 设置单元格格式
            CellStyle headerStyle = ExcelUtils.headerStyle(workbook);

            // 创建表头单元格
            String[] headers = {"ID", "姓名", "部门", "入职日期", "薪资"};

            // 填充数据
            ExcelUtils.writeCell(headers, headerRow, headerStyle);

            //6. 添加数据行
            Object[][] data = {
                    {1, "张三", "研发部", "2020-05-15", 8500.00},
                    {2, "李四", "市场部", "2019-11-03", 7800.50},
                    {3, "王五", "人事部", "2021-02-20", 9200.00}
            };

            for (int i = 0; i < data.length; i++) {
                Row row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue((Integer) data[i][0]);
                row.createCell(1).setCellValue((String) data[i][1]);
                row.createCell(2).setCellValue((String) data[i][2]);
                row.createCell(3).setCellValue((String) data[i][3]);
                row.createCell(4).setCellValue((Double) data[i][4]);
            }

            // 7. 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            workbook.write(new FileOutputStream(path));
        }

        LOGGER.info("Excel文件生成成功！");
    }

    public void advancedexcelgenerator (String path) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {

            // 创建工作表
            Sheet sheet = workbook.createSheet("销售报告");

            // 创建日期格式
            CellStyle dateStyle = workbook.createCellStyle();
            CreationHelper createHelper = workbook.getCreationHelper();
            dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy年m月d日"));

            // 创建货币格式
            CellStyle currencyStyle = workbook.createCellStyle();
            currencyStyle.setDataFormat((short) 8);

            // 表头
            String[] headers = {"产品", "销售日期", "数量", "单价", "总价"};
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(i).setCellValue(headers[i]);
            }

            // 数据
            Object[][] data = {
                    {"笔记本电脑", LocalDate.of(2023, 1, 15), 5, 5999.99},
                    {"智能手机", LocalDate.of(2023, 2, 3), 12, 3999.50},
                    {"平板电脑", LocalDate.of(2023, 3, 20), 8, 3299.00}
            };

            for (int i = 0; i < data.length; i++) {
                Row row = sheet.createRow(i + 1);

                // 产品名称
                row.createCell(0).setCellValue((String) data[i][0]);

                // 销售日期
                LocalDate localDate = (LocalDate) data[i][1];
                Date date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
                Cell dateCell = row.createCell(1);
                dateCell.setCellValue(date);
                dateCell.setCellStyle(dateStyle);

                // 数量
                row.createCell(2).setCellValue((Integer) data[i][2]);

                // 单价
                Cell priceCell = row.createCell(3);
                priceCell.setCellValue((Double) data[i][3]);
                priceCell.setCellStyle(currencyStyle);

                // 总价（使用公式）
                Cell totalCell = row.createCell(4);
                totalCell.setCellFormula("C" + (i + 2) + "*D" + (i + 2));
                totalCell.setCellStyle(currencyStyle);
            }

            // 添加汇总行
            Row summaryRow = sheet.createRow(data.length + 2);
            summaryRow.createCell(0).setCellValue("总计:");
            summaryRow.createCell(4).setCellFormula("SUM(E2:E" + (data.length + 1) + ")");
            summaryRow.getCell(4).setCellStyle(currencyStyle);

            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(new FileOutputStream(path));
        }

        LOGGER.info("Excel文件生成成功！");
    }
}
